use crate::error::new_err_pro_repe;
use crate::error::AppErr;
use crate::error::SqlxErr;
use serde::Serialize;
use sqlx::Executor;
use sqlx::Row;
use sqlx::SqliteConnection;

use super::get_pool;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_pro (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name TEXT NOT NULL, 
    description TEXT NOT NULL,
    UNIQUE(name)
)
"#;

pub async fn init() -> Result<(), SqlxErr> {
    let pool = get_pool();
    pool.execute(CREATE_SQL).await?;
    Ok(())
}

async fn create_impl(
    conn: &mut SqliteConnection,
    name: &str,
    description: &str,
) -> Result<i64, AppErr> {
    let ret = sqlx::query(r#"SELECT id FROM tb_pro WHERE name = ?"#)
        .bind(name)
        .fetch_optional(&mut *conn)
        .await?;
    if ret.is_some() {
        return Err(new_err_pro_repe());
    }
    let ret = sqlx::query(
        r#"
    INSERT INTO tb_pro (name, description) VALUES (?, ?)
    "#,
    )
    .bind(name)
    .bind(description)
    .execute(&mut *conn)
    .await?;
    Ok(ret.last_insert_rowid())
}

pub async fn create(name: &str, description: &str) -> Result<i64, AppErr> {
    let mut tx = get_pool().begin().await?;
    let id = create_impl(&mut tx, name, description).await?;
    tx.commit().await?;
    Ok(id)
}

#[derive(Debug, Serialize)]
pub struct Pro {
    id: i64,
    name: String,
    description: String,
}

pub async fn query() -> Result<Box<[Pro]>, SqlxErr> {
    let rows = sqlx::query(
        r#"
        SELECT id, name, description FROM tb_pro
    "#,
    )
    .fetch_all(get_pool())
    .await?;
    let mut vec = Vec::with_capacity(rows.len());
    for row in rows {
        let pro = Pro {
            id: row.get(0),
            name: row.get(1),
            description: row.get(2),
        };
        vec.push(pro);
    }
    Ok(vec.into_boxed_slice())
}

pub async fn del(id: i64) -> Result<(), SqlxErr> {
    sqlx::query(r#"DELETE FROM tb_pro WHERE id = ?"#)
        .bind(id)
        .execute(get_pool())
        .await?;
    Ok(())
}
